using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
namespace TeamTime.Classes
{
    public class Lookups
    {

        public Int16 getSampleTypeIDByDescrip(string sampleType)
        {
            Int16 ret = 0;
            if (sampleType != "")
            {
                string sql = "select ID from SampleType where '" + sampleType.Trim() + "' like Description+'%'";
                SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
                if (dr.Read())
                {
                    ret = Convert.ToInt16(dr[0]);
                }
                dr.Close();
            }
            return ret;
        }

        internal DataSet getGroupings(bool anyOption)
        {
            string sql = "select ID, Description from Grouping";
            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }
        //internal DataSet getTeams(bool anyOption)
        //{
        //    string sql = "select ID, Description from Team";
        //    if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
        //    return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        //}
        internal DataSet getStatuses(bool anyOption)
        {
            string sql = "select ID, Description from Status";
            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            sql += " order by 1";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getRoles(bool anyOption)
        {
            string sql = "select ID, Description from Role";
            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getGroupingMembers(string groupingName, bool anyOption)
        {
            string sql = "select p.ID, p.FullName " +
            " from ViewPersons p " +
            " inner join PersonGrouping pg on p.ID = pg.PersonID " +
            " inner join Grouping g on pg.GroupingID = g.ID and g.Description = @groupingName ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as FullName UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                                new SqlParameter("groupingName", groupingName));
        }

        internal DataSet getCompanies( bool anyOption)
        {
            string sql = "select c.ID, c.CompanyName " +
            " from Company c ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as CompanyName UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        

        internal DataSet getBusinessSectors(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
            " from BusinessSector t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getEnterpriseStatuses(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
            " from EnterpriseStatus t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getGradings(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
            " from Grading t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getOffices(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
            " from Office t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getTargetMarkets(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
            " from TargetMarket t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getActivities(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
           " from Activity t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }
     
        internal DataSet getProvinces(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
          " from Province t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            sql += " order by 2";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal DataSet getFundingFor(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
          " from FundingFor t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            sql += " order by 2";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        internal Int16 getSupplierServiceIDByDescrip(string Descrip)
        {
            Int16 ret = 0;
            if (Descrip != "")
            {
                string sql = "select ID from SupplierService where Description = @Descrip";
                SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                        new SqlParameter("Descrip", Descrip));
                if (dr.Read())
                {
                    ret = Convert.ToInt16(dr[0]);
                }
                dr.Close();
            }
            return ret;
        }

        internal DataSet getEventFocusAreas(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
          " from Events.EventFocusArea t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            sql += " order by 1";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }
        internal DataSet getEventTypes(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
          " from Events.EventType t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            sql += " order by 1";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        //internal DataSet getProvinces()
        //{
        //    return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, "SELECT ID,Description FROM Province ORDER BY Description ASC");
        //}
        //internal DataSet getCompanies()
        //{
        //    return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, "SELECT ID,CompanyName FROM Company ORDER BY CompanyName ASC");
        //}

        internal DataSet getFundingApplicationStatuses(bool anyOption)
        {
            string sql = "select t.ID, t.Description " +
          " from FundingApplicationStatus t ";

            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            sql += " order by 1";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }
    }
}
